package be;

import java.io.FileInputStream;
import java.sql.*;
import java.util.ArrayList;
import java.util.Properties;

import javax.sql.*;
import javax.naming.*;

public class db_backend implements backend{
	public Connection conn;

	public void connect2(){
		Properties prop = new Properties();
		//prop.put("user", "yuukiadm");
		//prop.put("password", "qoe7pvdmxox");
		prop.put("user", "yuuki");
		prop.put("password", "orka");
		try {
			//conn = DriverManager.getConnection("jdbc:mysql://mysql-vt2012.csc.kth.se/DD2483_yuuki?autoReconnect=true", prop);
			conn = DriverManager.getConnection("jdbc:mysql://130.229.128.198/test", prop);
		} catch (Exception e) {
			System.out.println("ERROOOOORRR!!! Connect!!");
			e.printStackTrace();
		}
	}
	
	public void connect(){
		connect2();
		/*
		try {
			Context initCtx = new InitialContext();
			Context envCtx = (Context) initCtx.lookup("java:comp/env");
			DataSource ds = (DataSource) envCtx.lookup("jdbc/db_lab1");
			conn = ds.getConnection();
		} catch (Exception e) {
			System.out.println("ERROOOOORRR!!! Connect!!");
			e.printStackTrace();
		}*/
	}
	
	public void disconnect(){
		try {
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public void CreateTable(String tableName, Table variables){
		connect();
		try{
			Statement stmt = conn.createStatement();
			String query = "CREATE TABLE " + tableName + "(";
			query +=  variables.getVars() + ")";
			
			System.out.println("Query; " + query);
			stmt.execute(query);
		}
		catch(SQLException e){
			
			System.out.println("SQL statement error!");
			e.printStackTrace();
		}
		
		disconnect();
	}
	
	public int Insert(String tableName, InsertList values){
		connect();
		
		try{
			Statement stmt = conn.createStatement();
			ResultSet rs;
			String query = "INSERT INTO " + tableName;
			
			query +=  values.getVars();

			//System.out.print(query);
			
			stmt.executeUpdate(query,Statement.RETURN_GENERATED_KEYS);
			
			rs = stmt.getGeneratedKeys();
			if ( rs.next() ) {
			    int key = rs.getInt(1);
			    disconnect();
			    return key;
			}
		}
		catch(SQLException e){
			disconnect();
			System.out.println("SQL statement error! INSERT");
			e.printStackTrace();
		}
		return -1;
	}
	
	public void storeBLOB(String tableName, FileInputStream file){
		//INSERT INTO table_name (column1, column2, column3,...)
		//VALUES (value1, value2, value3,...)
		connect();
		try{
			PreparedStatement pre = conn.prepareStatement("INSERT INTO " + tableName + "VALUES(?)");
			//System.out.println("File: Path:"+ aFile.getPath() + ",Name: " + aFile.getName());
			//FileInputStream inputS = new FileInputStream(file);
			pre.setBlob(1, file);
			
			System.out.println(pre.toString());
			pre.execute();
		}
		catch(SQLException e){
			System.out.println("SQL statement error! INSERT");
			e.printStackTrace();
		}
		
		disconnect();
	}
	
	public void UpdateData(String table, InsertList values, InsertList w){
		connect();
		
		try{
			Statement stmt = conn.createStatement();
			String query = "UPDATE " + table + " SET ";
			query +=  values.getUpdateVars();
			query += " WHERE " + w.getWhereVars() + ";";

			System.out.println("Query changed " + stmt.executeUpdate(query) + " rows.");
		}
		catch(SQLException e){
			disconnect();
			System.out.println("SQL statement error! INSERT");
			e.printStackTrace();
		}
	}
	
	
	/**
	 * REMEMBER TO CALL DISCONNECT IN MIDDLE AFTER USING THIS!
	 * Also, if the insertlists are null, select EVERYTHING!
	 * @param table - the table name
	 * @param Select - * OR name,email, osv
	 * @param where - userid = 2 AND blaba = sldfls
	 * @return
	 */
	public ResultSet getData(String table, InsertList select, InsertList where, String tail){
		
		connect();
		try{
			Statement stmt = conn.createStatement();
			ResultSet rs = null;
			String query;
			if(select != null)
				query = "select " + select.getSelectVars() + " from " + table;
			else
				query = "select * from " + table;
			
			//prolly unsafe... whatever...
			if(where != null)
				if(where.table.size() != 0)
					query += " WHERE "+where.getWhereVars();

			if(tail != null && tail != ""){
				query += " "+tail + ";";
			}
			//System.out.println("QUERY:" + query);
			
			rs = stmt.executeQuery(query);
			//Removed the disconnect, cuz we couldnt do things with the resultset we returns otherwise
			//rs.close();
			//disconnect();
			
			return rs;
		}
		catch(SQLException e){
			disconnect();
			System.out.println("SQL statement error!");
			e.printStackTrace();
			return null;
		}
	}
	/**
	 *  if data = "" removes everything!
	 * @param table - tableName
	 * @param data -  WHERE `id` = 3 AND sdfdsdf---
	 * 
	 */
	public void deleteData(String table,InsertList d){
		//DELETE FROM `test`.`kunder` WHERE `kunder`.`id` = 3
		//Data = "WHERE balbal = blaba AND albalbla = adla";
		connect();
		
		try{
			Statement stmt = conn.createStatement();
			String query = "DELETE FROM " + table;
			query += " WHERE " + d.getWhereVars() + ";";
			
			stmt.execute(query);
		}
		catch(SQLException e){
			System.out.println("SQL statement error!");
			e.printStackTrace();
		}
		disconnect();
	}
}